#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Date    : 2022/8/17 10:48
# @Author  : 招财大龙猫
# @File    : 流量分析.py
# @Description :
import datetime

import pandas as pd

data = [
    {
        "mac": "0C: 9D: 92:C3: 72:FB",
        "ip": "192.168.13.236",
        "dev": "",
        "os": "",
        "host": "",
        "id": "",
        "count": 0,
        "rlHost": 0,
        "rlDev": 0,
        "rlId": 0,
        "rlOs": 0,
        "flags": 0,
        "spkt": "60",
        "rpkt": "45",
        "sbyte": "13280",
        "rbyte": "2398",
        "track": [
            {
                "sbyte": "222",
                "rbyte": "368",
                "spkt": "6",
                "rpkt": "7",
                "src": 8848,
                "dst": 41218,
                "ip": "192.168.2.157",
                "protos": "",
                "sver": "S",
                "ptl": 6,
                "ttl": 0,
                "details": ""
            },
            {
                "sbyte": "222",
                "rbyte": "368",
                "spkt": "5",
                "rpkt": "7",
                "src": 8848,
                "dst": 41194,
                "ip": "192.168.2.157",
                "protos": "",
                "sver": "S",
                "ptl": 6,
                "ttl": 0,
                "details": ""
            },
            {
                "sbyte": "222",
                "rbyte": "368",
                "spkt": "6",
                "rpkt": "7",
                "src": 8848,
                "dst": 41202,
                "ip": "192.168.2.157",
                "protos": "",
                "sver": "S",
                "ptl": 6,
                "ttl": 0,
                "details": ""
            },
            {
                "sbyte": "222",
                "rbyte": "368",
                "spkt": "6",
                "rpkt": "7",
                "src": 8848,
                "dst": 41206,
                "ip": "192.168.2.157",
                "protos": "",
                "sver": "S",
                "ptl": 6,
                "ttl": 0,
                "details": ""
            },
            {
                "sbyte": "222",
                "rbyte": "368",
                "spkt": "6",
                "rpkt": "7",
                "src": 8848,
                "dst": 41210,
                "ip": "192.168.2.157",
                "protos": "",
                "sver": "S",
                "ptl": 6,
                "ttl": 0,
                "details": ""
            },
            {
                "sbyte": "0",
                "rbyte": "9780",
                "spkt": "16",
                "rpkt": "25",
                "src": 52567,
                "dst": 22,
                "ip": "172.16.16.172",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 0,
                "details": "PROTOCOL:SSH\\r\\n"
            }
        ]
    },
    {
        "mac": "E8: 68: 19:C9: 68:9E",
        "ip": "172.16.16.172",
        "dev": "",
        "os": "",
        "host": "",
        "id": "",
        "count": 0,
        "rlHost": 0,
        "rlDev": 0,
        "rlId": 0,
        "rlOs": 0,
        "flags": 0,
        "spkt": "16",
        "rpkt": "23",
        "sbyte": "320",
        "rbyte": "10072",
        "track": [
            {
                "sbyte": "9612",
                "rbyte": "0",
                "spkt": "23",
                "rpkt": "16",
                "src": 22,
                "dst": 52567,
                "ip": "192.168.13.236",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 1,
                "details": ""
            }
        ]
    },
    {
        "mac": "E8: 68: 19:C9: 68:9E",
        "ip": "192.168.2.157",
        "dev": "",
        "os": "",
        "host": "",
        "id": "",
        "count": 0,
        "rlHost": 0,
        "rlDev": 0,
        "rlId": 0,
        "rlOs": 0,
        "flags": 0,
        "spkt": "29",
        "rpkt": "34",
        "sbyte": "2078",
        "rbyte": "2960",
        "track": [
            {
                "sbyte": "368",
                "rbyte": "222",
                "spkt": "6",
                "rpkt": "6",
                "src": 41218,
                "dst": 8848,
                "ip": "192.168.13.236",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 1,
                "details": ""
            },
            {
                "sbyte": "368",
                "rbyte": "222",
                "spkt": "6",
                "rpkt": "5",
                "src": 41194,
                "dst": 8848,
                "ip": "192.168.13.236",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 1,
                "details": ""
            },
            {
                "sbyte": "368",
                "rbyte": "222",
                "spkt": "7",
                "rpkt": "6",
                "src": 41202,
                "dst": 8848,
                "ip": "192.168.13.236",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 1,
                "details": ""
            },
            {
                "sbyte": "368",
                "rbyte": "222",
                "spkt": "7",
                "rpkt": "6",
                "src": 41206,
                "dst": 8848,
                "ip": "192.168.13.236",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 1,
                "details": ""
            },
            {
                "sbyte": "368",
                "rbyte": "222",
                "spkt": "7",
                "rpkt": "6",
                "src": 41210,
                "dst": 8848,
                "ip": "192.168.13.236",
                "protos": "",
                "sver": "C",
                "ptl": 6,
                "ttl": 1,
                "details": ""
            }
        ]
    }
]


def make_data2db(data):
    """
    清洗数据将数据写入flowing_info 表格
    源数据格式： [{'mac': '0C:9D:92:C3:72:FB', 'ip': '192.168.13.236', 'dev': '', 'os': '', 'host': '', 'id': '', 'count': 0, 'rlHost': 0, 'rlDev': 0, 'rlId': 0, 'rlOs': 0, 'flags': 0, 'spkt': '60', 'rpkt': '45', 'sbyte': '13280', 'rbyte': '2398', 'track': [{'sbyte': '222', 'rbyte': '368', 'spkt': '6', 'rpkt': '7', 'src': 8848, 'dst': 41218, 'ip': '192.168.2.157', 'protos': '', 'sver': 'S', 'ptl': 6, 'ttl': 0, 'details': ''}, {'sbyte': '222', 'rbyte': '368', 'spkt': '5', 'rpkt': '7', 'src': 8848, 'dst': 41194, 'ip': '192.168.2.157', 'protos': '', 'sver': 'S', 'ptl': 6, 'ttl': 0, 'details': ''}, {'sbyte': '222', 'rbyte': '368', 'spkt': '6', 'rpkt': '7', 'src': 8848, 'dst': 41202, 'ip': '192.168.2.157', 'protos': '', 'sver': 'S', 'ptl': 6, 'ttl': 0, 'details': ''}, {'sbyte': '222', 'rbyte': '368', 'spkt': '6', 'rpkt': '7', 'src': 8848, 'dst': 41206, 'ip': '192.168.2.157', 'protos': '', 'sver': 'S', 'ptl': 6, 'ttl': 0, 'details': ''}, {'sbyte': '222', 'rbyte': '368', 'spkt': '6', 'rpkt': '7', 'src': 8848, 'dst': 41210, 'ip': '192.168.2.157', 'protos': '', 'sver': 'S', 'ptl': 6, 'ttl': 0, 'details': ''}, {'sbyte': '0', 'rbyte': '9780', 'spkt': '16', 'rpkt': '25', 'src': 52567, 'dst': 22, 'ip': '172.16.16.172', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 0, 'details': 'PROTOCOL:SSH\\r\\n'}]}, {'mac': 'E8:68:19:C9:68:9E', 'ip': '172.16.16.172', 'dev': '', 'os': '', 'host': '', 'id': '', 'count': 0, 'rlHost': 0, 'rlDev': 0, 'rlId': 0, 'rlOs': 0, 'flags': 0, 'spkt': '16', 'rpkt': '23', 'sbyte': '320', 'rbyte': '10072', 'track': [{'sbyte': '9612', 'rbyte': '0', 'spkt': '23', 'rpkt': '16', 'src': 22, 'dst': 52567, 'ip': '192.168.13.236', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 1, 'details': ''}]}, {'mac': 'E8:68:19:C9:68:9E', 'ip': '192.168.2.157', 'dev': '', 'os': '', 'host': '', 'id': '', 'count': 0, 'rlHost': 0, 'rlDev': 0, 'rlId': 0, 'rlOs': 0, 'flags': 0, 'spkt': '29', 'rpkt': '34', 'sbyte': '2078', 'rbyte': '2960', 'track': [{'sbyte': '368', 'rbyte': '222', 'spkt': '6', 'rpkt': '6', 'src': 41218, 'dst': 8848, 'ip': '192.168.13.236', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 1, 'details': ''}, {'sbyte': '368', 'rbyte': '222', 'spkt': '6', 'rpkt': '5', 'src': 41194, 'dst': 8848, 'ip': '192.168.13.236', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 1, 'details': ''}, {'sbyte': '368', 'rbyte': '222', 'spkt': '7', 'rpkt': '6', 'src': 41202, 'dst': 8848, 'ip': '192.168.13.236', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 1, 'details': ''}, {'sbyte': '368', 'rbyte': '222', 'spkt': '7', 'rpkt': '6', 'src': 41206, 'dst': 8848, 'ip': '192.168.13.236', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 1, 'details': ''}, {'sbyte': '368', 'rbyte': '222', 'spkt': '7', 'rpkt': '6', 'src': 41210, 'dst': 8848, 'ip': '192.168.13.236', 'protos': '', 'sver': 'C', 'ptl': 6, 'ttl': 1, 'details': ''}]}]
    :param data: [{},{},{},{}]
    :return: None
    """
    df = pd.DataFrame()
    if data:
        for i in data:
            try:
                mid_dict = i['track']
                mid_df = pd.DataFrame(mid_dict)
                mid_df['sip'] = i['ip']
                if len(mid_df):
                    df = pd.concat([df, mid_df], axis=0)
            except Exception as exp:
                print(f'处理流量数据报错，数据结构不对 {exp}, ---> {i}')
                continue

        df_c = df.query("sver == 'C'").copy()
        df_s = df.query("sver == 'S'").copy()
        if len(df_c):
            df_c["c_flow"] = df_c["sbyte"].astype(float) + df_c['rbyte'].astype(float)
            df_c = df_c.rename(
                columns={'ip': 'c_ip', 'src': 's_port', 'dst': 'port', 'sip': 'ip', 'c_flow': 'c_flow'})
            df_c['s_ip'] = None
            df_c['s_flow'] = 0.0
        if len(df_s):
            df_s["s_flow"] = df_s["sbyte"].astype(float) + df_s["rbyte"].astype(float)
            df_s = df_s.rename(
                columns={'ip': 's_ip', 'src': 'port', 'dst': 's_port', 'sip': 'ip', 's_flow': 's_flow'})
            df_s['c_ip'] = None
            df_s['c_flow'] = 0.0

        df = pd.concat([df_c, df_s], axis=0)
        if len(df):
            df['id'] = range(len(df))
            df.reset_index(drop=True, inplace=True)
            df_result = df[['id', 'ip', 'port', 'c_ip', 'c_flow', 's_ip', 's_port', 's_flow']].copy()
            df_result['add_time'] = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            result = df_result.to_json()
        else:
            result = df.to_json()
    else:
        result = df.to_json()

    print('******result*******', result)
    return result


if __name__ == '__main__':
    make_data2db(data)
